﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using ws_sismun.Models;

namespace ws_sismun.Services
{
    public class VecinoRepository
    {

        public VecinoModel[] GetAllVecinos( string TipoDocumento, string NumeroDocumento, string Nombre, string Sector, string SubSector )
        {

            List<VecinoModel> data = new List<VecinoModel>();

            var db = new Model1Container();

            StringBuilder builder = new StringBuilder();

            object[] parameters = null;

            if (Nombre.Equals("none"))
            {
                builder.AppendLine(" SELECT     VecinoSet.Nombre, VecinoSet.NroDocumento, VecinoSet.TipoDocumento, VecinoSet.Direccion, VecinoSet.Correo, VecinoSet.Id, SectorSet.Id AS SectorId,");
                builder.AppendLine(" SubSectorSet.Id AS SubSectorId ");
                builder.AppendLine(" FROM         SectorSet INNER JOIN ");
                builder.AppendLine(" SubSectorSet ON SectorSet.Id = SubSectorSet.SectorId INNER JOIN");
                builder.AppendLine(" VecinoSector ON SectorSet.Id = VecinoSector.Sector_Id INNER JOIN");
                builder.AppendLine(" VecinoSet ON VecinoSector.Vecino_Id = VecinoSet.Id");
                builder.AppendLine(" WHERE     (VecinoSet.NroDocumento = {1}) AND (VecinoSet.TipoDocumento = {0}) AND (SectorSet.Id = {2}) AND (SubSectorSet.Id = {3}) ");
                builder.AppendLine(" ORDER BY VecinoSet.Nombre");

                
                parameters = new object[] { TipoDocumento, NumeroDocumento, Sector, SubSector };
            }
            else
            {
                builder.AppendLine(" SELECT     VecinoSet.Nombre, VecinoSet.NroDocumento, VecinoSet.TipoDocumento, VecinoSet.Direccion, VecinoSet.Correo, VecinoSet.Id, SectorSet.Id AS SectorId,");
                builder.AppendLine(" SubSectorSet.Id AS SubSectorId ");
                builder.AppendLine(" FROM         SectorSet INNER JOIN ");
                builder.AppendLine(" SubSectorSet ON SectorSet.Id = SubSectorSet.SectorId INNER JOIN");
                builder.AppendLine(" VecinoSector ON SectorSet.Id = VecinoSector.Sector_Id INNER JOIN");
                builder.AppendLine(" VecinoSet ON VecinoSector.Vecino_Id = VecinoSet.Id");
                builder.AppendLine(" WHERE     (VecinoSet.Nombre LIKE {0}) AND (SectorSet.Id = {1}) AND (SubSectorSet.Id = {2}) ");
                builder.AppendLine(" ORDER BY VecinoSet.Nombre");
                
                parameters = new object[] { "%"+Nombre+"%", Sector, SubSector };
            }

            
            var query = db.VecinoSet.SqlQuery( builder.ToString(), parameters );

            foreach ( var e in query ){
                VecinoModel v = new VecinoModel();
                v.Id = e.Id;
                v.Correo = e.Correo;
                v.Direccion = e.Direccion;
                v.NumeroDocumento = e.NroDocumento;
                v.TipoDocumento = e.TipoDocumento;
                v.Nombre = e.Nombre;

                data.Add(v);

                
            }
            
            db.Dispose();

            return data.ToArray<VecinoModel>();

        }
    }
}